[初心者向け]Amazon Redshift内のデータをできる限り楽にSpectrum化してみた

Amazon RedshiftからparquetでUNLOADしてSpectrum化してみた。

#Redshift Spectrum

#Amazon Redshift

#AWS

岩澤暁海

2020.02.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

去年行われたre:Invent2019の2日目のキーノートでAmazon Redshift の新機能「Data Lake Export」が発表されました。
[速報] Amazon Redshift の新機能「Data Lake Export」でParquet形式にUNLOADできるようになりました #reinvent ｜ Developers.IO

これにより自前で変換プログラムを用意せずともs3へのparquetファイル形式で出力することが可能になりました。
~~自前で作る必要なくなったよ、やったね！~~

そこで今回は、できる限り楽してAmazon Redshift上のデータをparquet形式のファイルにしてAmazon Redshift Spectrum化できるかやってみました。

作業一覧

1) テスト用データ作成
3) Amazon Redshift用のIAMロールの作成
3) 作成したIAMロールを設定する
4) UNLOAD先バケットの準備
5) parquet形式のUNLOADを行う
6) 外部スキーマの作成
7) Glueのクローラを使う
8) データの確認

テスト用データ作成

おなじみのステップ 6: Amazon S3 のサンプルデータをロードする - Amazon Redshiftのデータを使います。

Amazon Redshift用のIAMロールの作成

Amazon Redshift用に割り振る、S3とGlueへのアクセス権を設定したIAMロールを作成します。

Managed Consoleを開き、IAMのページに移動し、左側から[ロール]を選択し、[ロールの作成]を押下します。

[サービスの選択]で"Redshift"を選択し、[ユースケースの選択]では"Redshift - Customizable"を選択します。右下の次へボタンを押下します。

アクセス付与として[ポリシーのフィルタ]に"AmazonS3FullAccess"と入力し、同ポリシーが表示されたらチェックを入れます。

今度は[ポリシーのフィルタ]に"AWSGlueConsoleFullAccess"と入力して同じようにチェックを入れます。

[ロールの作成]画面では[ロール名]を記載し、[ロールの作成]を押下します。

これでAmazon Redshiftへ追加するロールが完成しました。
次の手順に進む前にロールARNを確認します。

ロールを選択すると概要ページに遷移しますので、ロールARNをどこかにコピーしておきます。

Amazon Redshiftに作成したIAMロールを追加する

続いて、Amazon Redshiftのクラスタに作成したIAMロールを追加する作業を行います。

[クラスター]を選択し、一覧から対象のクラスターを選択します。

[プロパティ]タグを選択し、[クラスターのアクセス許可]の[IAMロールの管理]を押下します。

[IAMロール]にて先ほど作成したロールを選択し、[IAMロールを追加]を押下します。

IAMロール一覧にロールが追加されていることを確認したら[Done]を押下します。

[クラスターのアクセス許可]のアタッチされたIAMロールの一覧に追加されていることを確認します。
※最初は状態が"adding"ですが時間経過で"in-sync"になります。

これでAmazon Redshiftへのロール設定は完了です。

UNLOAD先バケットの準備

最後の事前準備としてUNLOAD先のバケットを用意します。
このとき、Redshiftと同じリージョン を指定してバケットを作成します。

※UNLOAD自体は別リージョンのバケットを指定して実行することも可能です。
しかし、Spectrum用のデータは同じリージョンのs3を使う必要があります。
（別リージョンのデータを指定すると最後の最後でエラーが出て悲しいことになります(なった)）

parquet形式のUNLOADを行う

ついにUNLOADコマンドの実行です。
今回は以下のsqlを実行します。

UNLOAD ('
  SELECT sales.*, date.*, total_price, percentile
    FROM sales, date, (
      SELECT eventid,total_price, ntile(1000) over (order by total_price desc) / 10.0 as percentile
        FROM (
          SELECT eventid, sum(pricepaid) total_price
            FROM sales
           GROUP BY eventid
        )
    ) as percentile_events
   WHERE sales.dateid = date.dateid
     AND percentile_events.eventid = sales.eventid
')
TO 's3://cm-a-iwa-spectrum-test/DataLake/SalesPartitioned/'
FORMAT AS PARQUET
PARTITION BY (year, caldate)
CREDENTIALS 'aws_iam_role=arn:aws:iam::123412341234:role/XXXXXXXXXXXXXXRole'
;

実行結果はこんな感じになります。

外部スキーマの作成

外部スキーマ("cm_a_iwa_spectrum_testdb")の作成を行います。同時にGlueのDataCatalogにデータベース("cm_a-iwa_db")を登録します。

CREATE EXTERNAL SCHEMA cm_a_iwa_spectrum_testdb FROM DATA CATALOG 
DATABASE 'cm_a-iwa_db'
IAM_ROLE 'arn:aws:iam::123412341234:role/XXXXXXXXXXXXXXRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

Managed ConsoleにてGlueのページを開き、データベース"cm-a-iwa-spectrum-testdb"が登録されていることを確認します。

クローラの作成と実行

最後にGlueのクローラを作成・実行し、DataCatalogにテーブルを登録します。

左ナビゲーションペインより[クローラ]を選択し、[クローラの追加]を押下します。

[クローラの情報]では[クローラの名前]を設定し(ここでは"cm_a-iwa_crawler"をしています)、[次へ]を押下します。

[Crawler source type]では"Data stores"を選択し、[次へ]を押下します。

[データストア]では
[データストアの選択]："S3"
[クロールするデータの場所]："自分のアカウントで指定されたパス"
[インクルードパス]：UNLOADコマンドのTO句で指定したprefix
を設定し、[次へ]を押下します。

[別のデータストアの追加]は"いいえ"を選択し、[次へ]を押下します。

[IAM ロールの選択]では"IAMロールを作成する"を選択し、ロール名を入力します(画像では"cm-a-iwa-spectrum-test")。[次へ]を押下します。

[このクローラのスケジュールを設定する]では実行スケジュールを設定します。
今回は手動で実行するので[頻度]を"オンデマンドで実行"を選択します。
[次へ]を押下します。

[クローラの出力を設定する]ではテーブルを追加するデータベースを設定します。
[データベース]にはRedshiftにて外部スキーマを作成際に設定した名称(例では"cm_a-iwa_db")を設定します。
[次へ]を押下します。

最後に確認画面が表示されるので、設定を確認して問題なければ[完了]を押下します。

これでクローラができました。一覧から作成したクローラにチェックをつけ、[クローラの実行]を押下します。

左ナビゲーションペインより[データベース]の[テーブル]にて、テーブルが追加されたことが確認できます。

確認してみる

それではSQLを実行して確認してみます。

無事、データ取得できました。

最後に

UNLOADファイルのparquet化プログラムを作成する必要がなくなったので、出力クエリと外部スキーマの作成だけで簡単にSpectrum化することができました。
Glueのクローラは周期実行の設定も可能ですので、集計用SQLを周期実行している環境ならば、集計処理結果をSpectrum化することも簡単かと思います。

それではまた！

おまけ:別リージョンにあるs3のバケットを指定すると？

UNLOADコマンドはREGION句があり、別リージョンへ出力することも可能です（時間はかかりますが）。
また外部スキーマ作成時に、別リージョンにあるGlue Catalogのデータベースを指定することも可能です。

クローラの作成時にも、別リージョンのs3を選択するとエラーメッセージが表示されます。

ですがを直接記述すると別リージョンのs3を指定できます。

そうして別リージョンのs3を参照したテーブルに対してクエリを実行すると...？

XX000: S3 Query Exception: 
  -----------------------------------------------
  error:  S3ServiceException:The S3 bucket addressed by the query is in a different region from this cluster.,Status 301,Error PermanentRedirect,Rid 616129725D1FE41C,ExtRid VB2RxbEl9uNn

はい、エラーが返ってきます。動きません。一回ひっかかりました。

※Amazon Redshift Spectrum に関する考慮事項に、 Amazon Redshift クラスターおよび Amazon S3 バケットは同じ AWS リージョンに存在する必要があります。 と記載があります。

ですので、このエラーメッセージを見た際はリージョンを確認するといいと思います。今度こそ...それではまた！